package com.pocketmenu.persistence;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.pocketmenu.business.Constants;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class OrderDataSource {
	
	// Database fields
	  private SQLiteDatabase database;	  
	  
	  private SQLiteHelper dbHelper;
	  private String[] allColumns = 
		  { SQLiteHelper.COLUMN_ID,
			SQLiteHelper.COLUMN_ORDER_TOTAL,
			SQLiteHelper.COLUMN_ORDER_SENT,			
			SQLiteHelper.COLUMN_ORDER_TABLE_CODE,
			SQLiteHelper.COLUMN_ORDER_NOTE,
			SQLiteHelper.COLUMN_ORDER_DATETIME};

	  public OrderDataSource(Context context) {
	    dbHelper = new SQLiteHelper(context);
	  }

	  public void open() throws SQLException {
	    database = dbHelper.getWritableDatabase();	    
	  }

	  public void close() {
	    dbHelper.close();
	  }

	  public Order create(int total, boolean sent, String tableCode, String note, Date datetime) {
	    ContentValues values = new ContentValues();
	    values.put(SQLiteHelper.COLUMN_ORDER_TOTAL, total);
	    values.put(SQLiteHelper.COLUMN_ORDER_SENT, sent);
	    values.put(SQLiteHelper.COLUMN_ORDER_TABLE_CODE, tableCode);
	    values.put(SQLiteHelper.COLUMN_ORDER_NOTE, note);
	    
	    SimpleDateFormat dateFormat = new SimpleDateFormat(Constants.DATETIME_FORMAT);	    
	    values.put(SQLiteHelper.COLUMN_ORDER_DATETIME, dateFormat.format(datetime));
	    
	    long insertId = database.insert(SQLiteHelper.TABLE_ORDER, null,
	        values);
	    
	    Cursor cursor = database.query(SQLiteHelper.TABLE_ORDER,
	        allColumns, SQLiteHelper.COLUMN_ID + " = " + insertId, null,
	        null, null, null);
	    cursor.moveToFirst();
	    Order newTemp = cursorToObject(cursor);
	    cursor.close();
	    return newTemp;
	  }

	  public void delete(Order toDelete) {
	    int id = toDelete.getId();
	    System.out.println("Order deleted with id: " + id);
	    database.delete(SQLiteHelper.TABLE_ORDER, SQLiteHelper.COLUMN_ID
	        + " = " + id, null);
	  }

	  public List<Order> getAll() {
	    List<Order> listTemp = new ArrayList<Order>();

	    Cursor cursor = database.query(SQLiteHelper.TABLE_ORDER,
	        allColumns, null, null, null, null, null);

	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      Order temp = cursorToObject(cursor);
	      listTemp.add(temp);
	      cursor.moveToNext();
	    }
	    // Make sure to close the cursor
	    cursor.close();
	    return listTemp;
	  }

	  private Order cursorToObject(Cursor cursor) {
	    Order temp = new Order();
	    temp.setId(cursor.getInt(0));
	    temp.setTotal(cursor.getInt(1));
	    
	    boolean sent = cursor.getInt(2) !=0 ;
	    temp.setSent(sent);
	    
	    temp.setTableCode(cursor.getString(3));
	    temp.setNote(cursor.getString(4));
	    
	    SimpleDateFormat format = new SimpleDateFormat(Constants.DATETIME_FORMAT);
	    Date datetime;
		try {
			datetime = format.parse(cursor.getString(5));
			temp.setDatetime(datetime);
		} catch (ParseException e) {				
			System.out.print("Error getting date from database [OrderDataSource:cursorToObject]");
		}	
			
	    return temp;
	  }
	  
	  public OrderItem cursorToObjectOrderItem(Cursor cursor) {
		  OrderItem temp = new OrderItem();
		    temp.setId(cursor.getInt(4));		       
		    temp.setQuantity(cursor.getInt(3));	    
		    temp.setIdItem(cursor.getInt(0));
		    temp.setIdOrder(cursor.getInt(5));
		    return temp;
	  }
	  
	  public Cursor getOrderItemList(int idOrder)
	  {
		  String query = "select i." + SQLiteHelper.COLUMN_ID 
				  + ", i." + SQLiteHelper.COLUMN_ITEM_NAME 
				  + ", i." + SQLiteHelper.COLUMN_ITEM_PRICE 
				  + ", oi." + SQLiteHelper.COLUMN_ORDER_ITEM_QUANTITY 
				  + ", oi." + SQLiteHelper.COLUMN_ID 
				  + ", oi." + SQLiteHelper.COLUMN_ORDER_ITEM_ID_ORDER 
				  + " from " + SQLiteHelper.TABLE_ORDER_ITEM + " oi join " + SQLiteHelper.TABLE_ITEM + " i on oi." + SQLiteHelper.COLUMN_ORDER_ITEM_ID_ITEM + " = i." + SQLiteHelper.COLUMN_ID + " where oi." + SQLiteHelper.COLUMN_ORDER_ITEM_ID_ORDER + " = " + idOrder;		  		  
		  Cursor cursor = database.rawQuery(query, null);				  		  
		  return cursor;
	  }
	  
	  public void updateOrder(int total, boolean sent, String tableCode, String note, Date datetime, int idOrder)
	  {
		  int sentInt = sent ? 1 : 0;
		  SimpleDateFormat format = new SimpleDateFormat(Constants.DATETIME_FORMAT);
		  
		  ContentValues values = new ContentValues();
		  values.put(SQLiteHelper.COLUMN_ORDER_TOTAL, String.valueOf(total));
		  values.put(SQLiteHelper.COLUMN_ORDER_SENT, String.valueOf(sentInt));
		  values.put(SQLiteHelper.COLUMN_ORDER_TABLE_CODE, tableCode);
		  values.put(SQLiteHelper.COLUMN_ORDER_NOTE, note);
		  values.put(SQLiteHelper.COLUMN_ORDER_DATETIME, format.format(datetime));
		  
		  String whereClause = SQLiteHelper.COLUMN_ID + "=" + idOrder;
		  database.update(SQLiteHelper.TABLE_ORDER, values, whereClause, null);  
	  }
	  
	  public int addOrderItem(int price, int idItem, int idOrder)
	  {
		  Order orderTemp;
		  if(idOrder == 0)
		  {
			  deleteOrderAll();
			  
			  Date now = new Date();
			  orderTemp = create(price, false, Constants.tableCode, "", now);			  
			  idOrder = orderTemp.getId();
		  }
		  else
		  {
			  Cursor cursor = database.query(SQLiteHelper.TABLE_ORDER,
				        allColumns, SQLiteHelper.COLUMN_ID + " = " + idOrder 
				        , null, null, null, null);
			  cursor.moveToFirst();
			  orderTemp = cursorToObject(cursor);
			  int total = orderTemp.getTotal();
			  			  
			  updateOrder(total + price, orderTemp.isSent(), Constants.tableCode, orderTemp.getNote(), orderTemp.getDatetime(), idOrder);
			  
			  idOrder = orderTemp.getId();
			  cursor.close();
		  }		  
		  		  
		  addOrderItem(idItem, idOrder);
		  return idOrder;
	  }
	  
	private void deleteOrderAll() {		
	    database.delete(SQLiteHelper.TABLE_ORDER, null, null);
	}

	private OrderItem addOrderItem(int idItem, int idOrder) {
		  
		  Cursor cursorOrderItem = database.query(SQLiteHelper.TABLE_ORDER_ITEM,
			        OrderItemDataSource.allColumns, SQLiteHelper.COLUMN_ORDER_ITEM_ID_ITEM + " = " + idItem + " and " + SQLiteHelper.COLUMN_ORDER_ITEM_ID_ORDER + " = " + idOrder
			        , null, null, null, null);
		  
		  cursorOrderItem.moveToFirst();
		  OrderItem orderItemTemp;
		  
		  ContentValues values = new ContentValues();	    	   
		    	   
		    values.put(SQLiteHelper.COLUMN_ORDER_ITEM_ID_ITEM, idItem);
		    values.put(SQLiteHelper.COLUMN_ORDER_ITEM_ID_ORDER, idOrder);
		    
		  if(cursorOrderItem.getCount() > 0)
		  {
			  orderItemTemp = OrderItemDataSource.cursorToObject(cursorOrderItem);			  
			  values.put(SQLiteHelper.COLUMN_ORDER_ITEM_QUANTITY, orderItemTemp.getQuantity() + 1);
			  
			  String whereClause = SQLiteHelper.COLUMN_ID + "=" + orderItemTemp.getId();
			  database.update(SQLiteHelper.TABLE_ORDER_ITEM, values, whereClause, null);
		  }
		  else
		  {
			  values.put(SQLiteHelper.COLUMN_ORDER_ITEM_QUANTITY, 1);
			  long insertId = database.insert(SQLiteHelper.TABLE_ORDER_ITEM, null,
				        values);
			  
			  Cursor cursorOrderItemTemp = database.query(SQLiteHelper.TABLE_ORDER_ITEM,
				        OrderItemDataSource.allColumns, SQLiteHelper.COLUMN_ID + " = " + insertId, null,
				        null, null, null);
			  
			  cursorOrderItemTemp.moveToFirst();			  
			  orderItemTemp = OrderItemDataSource.cursorToObject(cursorOrderItemTemp);	
			  cursorOrderItemTemp.close();
		  }

		    cursorOrderItem.close();
		    return orderItemTemp;
	  }
	  
	  public void deleteOrderItem(OrderItem toDelete) {		  		  		    		    
		  Cursor cursorOrderItem = database.query(SQLiteHelper.TABLE_ORDER_ITEM,
				  OrderItemDataSource.allColumns, SQLiteHelper.COLUMN_ID + " = " + toDelete.getId()
			        , null, null, null, null);
		  
		  cursorOrderItem.moveToFirst();		  
		  		    
		  if(cursorOrderItem.getCount() > 0)
		  {			  			  
			  if(toDelete.getQuantity() > 1)
			  {			  
				  ContentValues values = new ContentValues();	    	   

				  values.put(SQLiteHelper.COLUMN_ORDER_ITEM_QUANTITY, toDelete.getQuantity() - 1);
				  values.put(SQLiteHelper.COLUMN_ORDER_ITEM_ID_ORDER, toDelete.getIdOrder());
				  values.put(SQLiteHelper.COLUMN_ID, toDelete.getId());
				  values.put(SQLiteHelper.COLUMN_ORDER_ITEM_ID_ITEM, toDelete.getIdItem());
				  
				  String whereClause = SQLiteHelper.COLUMN_ID + "=" + toDelete.getId();
				  database.update(SQLiteHelper.TABLE_ORDER_ITEM, values, whereClause, null);
			  }
			  else
			  {
				  database.delete(SQLiteHelper.TABLE_ORDER_ITEM, SQLiteHelper.COLUMN_ID
				        + " = " + toDelete.getId(), null);			  			  			  
			  }

		  }
		  
		  cursorOrderItem.close();
	  }	  	 
	  
	  public Order getOrder(int id)
	  {		  
		  Cursor cursor = database.query(SQLiteHelper.TABLE_ORDER, allColumns, SQLiteHelper.COLUMN_ID + " = " + id, null, null, null, null);
		  cursor.moveToFirst();
		  Order order = cursorToObject(cursor);
		  cursor.close();
		  return order;
	  }
	  
	  public Cursor getAllOrders()
	  {
		  return database.query(SQLiteHelper.TABLE_ORDER, allColumns, SQLiteHelper.COLUMN_ID + " != " + Constants.idOrder, null, null, null, null);
	  }
}
